package com.xianzhiOA.controller;

import com.xianzhiOA.bean.XzAttendance;
import com.xianzhiOA.service.XzAttendanceService;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.servlet.ModelAndView;
import javax.servlet.http.HttpServletRequest;
import java.io.*;
import java.text.SimpleDateFormat;
import java.util.Date;


/**
 * 考勤表excel的导入 针对公司打卡机
 *
 * @author 郭云春晓
 * @create 2017-04-24 15:49
 **/
@Controller
@RequestMapping("UploadExcel")
public class UploadController {
    @Autowired
    private XzAttendanceService xzAttendanceService;

    @RequestMapping("upload.emp")
    public ModelAndView upload(@RequestParam(value = "file")MultipartFile file,XzAttendance xzAttendance, HttpServletRequest request){
        ModelAndView mv = new ModelAndView("backEnd/attendanceImport");
        File newFile = new File(file.getOriginalFilename());//获取文件名及后缀
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm");
        SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy-MM-dd");
        String datestr;//日期字符串
        int empNo;//工号
        String str;//临时字符串
        String str2;//临时字符串
        String weekday;//星期
        String firststr;//上班时间字符串
        String laststr;//下班时间字符串
        String string;//最终字符串
        Date firstTime;//上班时间
        Date lastTime;//下班时间
        try {
            file.transferTo(newFile);
            InputStream is = new FileInputStream(newFile);
            Workbook workbook = new HSSFWorkbook(is);
            //打卡机导出的有效sheet从4开始0,1,2,3,4...
            int sheetNo;
            int sheets = workbook.getNumberOfSheets();//获取工作表总数量
            //从sheet4开始循环读取，每一个sheet里有3名员工
            for(sheetNo=4;sheetNo<sheets;sheetNo++){
                Sheet sheet = workbook.getSheetAt(sheetNo);//得到工作表
                Row row = sheet.getRow(0);             //excel对应的行
                Row row2;                   //备用的行
                Cell cell = null;           //Excel对应的单元格
                Cell cell2 = null;          //备用的单元格
                int totalRow = sheet.getLastRowNum();//得到Excel的总记录条数
                int totalCell = row.getLastCellNum();//得到一行中有效的单元格数
                //获取年份月份
                row = sheet.getRow(1);
                cell = row.getCell(3);
                datestr = cell.getStringCellValue();
                String[] tstr = datestr.split("~");
                datestr = tstr[0];
                String[] tt = datestr.split("/");
                //年月
                datestr = tt[0]+"-"+tt[1];
                for (int i = 9; i <totalCell ; i=i+15) {//列
                    //获取工号,表格中的第4行为工号固定位置
                    row = sheet.getRow(3);//工号所在行
                    row2 = sheet.getRow(2);//姓名所在行
                    if (i==9||i==24||i==39){
                        cell = row.getCell(i);//工号所在单元格
                        cell2 = row2.getCell(i);//姓名所在单元格
                        str = cell.getStringCellValue();//得到工号
                        if (str.equals("")||str==null){
                            break;
                        }
                        empNo = Integer.parseInt(str);
                        str2 = cell2.getStringCellValue();//得到姓名
//                        System.out.println(empNo+"号员工____"+str2);
                        //日期循环 第12行开始有效记录考勤时间
                        for (int j = 11; j < totalRow; j++) {//行
                            row = sheet.getRow(j);
                            cell = row.getCell(0);
                            str = cell.getStringCellValue();
//                            System.out.print(str);//日号与星期
                            String[] dd = str.split("");
                            str = dd[0]+dd[1];//日号
                            weekday = dd[3];//星期
//                            System.out.println("---星期"+weekday);
                            str2 = datestr+"-"+str;
//                            System.out.println("***当前年月日："+str2);
                            int first;//上班单元格
                            int last;//下班单元格
                            if (i==9){
                                first=1;
                                last=8;
                                cell = row.getCell(first);
                                cell2 = row.getCell(last);
                                if(cell2.getStringCellValue().equals("")){//5点半之后下班判断
                                    cell2 = row.getCell(last+2);
                                }
                            }else if (i==24){
                                first=16;
                                last=23;
                                cell = row.getCell(first);
                                cell2 = row.getCell(last);
                                if(cell2.getStringCellValue().equals("")){//5点半之后下班判断
                                    cell2 = row.getCell(last+2);
                                }
                            }else if (i==39){
                                first=31;
                                last=38;
                                cell = row.getCell(first);
                                cell2 = row.getCell(last);
                                if(cell2.getStringCellValue().equals("")){//5点半之后下班判断
                                    cell2 = row.getCell(last+2);
                                }
                            }
                            //获取上班时间
                            firststr = cell.getStringCellValue();
                            //记录当天日期
                            xzAttendance.setDate(sdf2.parse(str2));
                            //如果没有上班时间记录，状态设置为0，
                            if (firststr.equals("")){
                                xzAttendance.setState(0);
                                xzAttendance.setEmpNo(empNo);
                                xzAttendance.setFirstTime(null);
                                xzAttendance.setLastTime(null);
                                //数据插入
                                xzAttendanceService.insert(xzAttendance);
                            }else {
                                string = str2+" "+firststr;//拼接最终插入数据库的字符串
//                            System.out.println(string);
                                firstTime = sdf.parse(string);
//                            System.out.println("===上班时间"+firstTime);
                                //获取下班时间
                                laststr = cell2.getStringCellValue();
                                if (!laststr.equals("")){
                                    string = str2+" "+laststr;
                                    lastTime = sdf.parse(string);
                                }else {
                                    lastTime = null;
                                }
//                            System.out.println("===下班时间"+lastTime);
                                xzAttendance.setEmpNo(empNo);
                                xzAttendance.setFirstTime(firstTime);
                                xzAttendance.setLastTime(lastTime);
                                xzAttendance.setState(1);
                                //数据插入
                                xzAttendanceService.insert(xzAttendance);

                            }


                        }
                    }
                }
            }
            is.close();//关闭流
        } catch (Exception e) {
            e.printStackTrace();
            mv.addObject("msg","导入失败");
        }
        mv.addObject("msg","导入成功");
        return mv;
    }
}
